空与非空：浅谈非空约束的影响

黄玮（Fuyuncat）

资深Oracle DBA，个人网，致力于数据库底层技术的研究，其作品获得广大同行的高度评价.

非空约束是字段的一个重要属性。但是，很多时候，数据库表的设计人员似乎并不十分在意这个属性。最常见的现象就是，除了主键字段外，所有字段都不指定该属性。而在Oracle中，默认是允许为空。

而实际上，优化器在选择执行计划时，非空约束是一个重要的影响因素。为了说明问题，我们建立以下测试表，然后分别说明非空约束在各种情况下对执行计划和性能的影响。

谓词评估

在上面表中，字段SUBOBJECT_NAME中不存在空值，但也没有非空约束，再看以下查询，查找该字段的空值记录：

统计信息如下:

我们看到，需要对表进行全表扫描（关于索引，随后再讨论）。而如果我们加上非空约束，可以看到执行计划已经性能的变化：

统计信息如下：

注意到，在全表扫描之前，增加了一个filter，而fileter的表达式是NULL IS NOT NULL，其逻辑结果是FALSE，因此，实际上其子操作（全表扫描）并未执行。相应的，请性能数据里面CR为0。

之所以优化器会为执行计划增加这样一个filter，是因为优化器在做查询转换（Query Transformation）时，会将非空约束作为参照条件之一，对where子句的谓词做逻辑结果评估，如果评估结果为false，则会增加一个这样的filter，以避免执行一些高代价的操作。从10053跟踪文件中，可以看到这对于优化器对执行计划代价估算的影响：

非空约束对索引选择的影响

我们知道，Oracle中B*树索引中不存在空键值，即在表的数据记录中，如果索引中所有字段都为空，则该记录不会被构建到索引树中。也就是说，如果索引字段上没有非空约束，则表记录与索引记录不是完全映射的。

我们先去掉subobject_name上的非空约束，并在上面建立索引：

HELLODBA.COM>alter table t_test1 modify subobject_name null;    

Table altered.  

HELLODBA.COM>create index t_test1_subo_idx on t_test1(subobject_name) compute statistics;   

Index created. 

 执行以下语句，以获取subobject_name最小的10条记录。为了提高效率，我们希望直接从索引中直接读取前10条ROWID（索引数据已经按照subobject_name排序），然后根据ROWID获取数据记录：

我们来看执行计划

统计信息如下：

但是，查询计划和结果看，语句并没有按照设想的方式执行，得出的数据也不是我们需要的。其原因就在于，由于空值不被索引，优化器无法确认索引数据是否涵盖了所有数据记录，因而它没有选择指定索引。

我们把非空约束加上，执行计划和结果就符合我们的需求了。

我们来看执行计划

统计信息如下

非空约束对连接查询的影响

在进行数据关联时，数据集中关联字段是否存在空值也会影响优化器对执行计划的选择。我们再创建一张测试表。

再将subobject_name的非空约束去掉。

HELLODBA.COM>alter table t_test1 modify subobject_name null;  

Table altered.  

我们通过以下语句查找t_test1中subobject_name不为table_name的数据：

统计信息如下：

可以看到，执行计划通过添加函数LNNVL和NOT EXISTS，对数据进行过滤得到结果，性能相当低。

注意：当逻辑表达是中的操作数可能为空时，LNNVL函数可以判断出该表达式的结果。

我们再把非空约束加上，

统计信息如下

可以看到执行计划通过ANTI-JOIN获取我们需要的数据，性能大为改善。

我们知道，对于空值的逻辑判断，只能通过IS NULL或IS NOT NULL进行判断，除此之外，一旦表达式中含有NULL值，结果始终为空。这也就是空值的存在不会影响JOIN或SEMI-JOIN的原因。

ANTI-JOIN，通过关联方法（NESTED-LOOP、MERGE、HASH）判断记录是否符合条件，一旦发现两边记录可以关联上，则判定不符合要求，可以放弃对一个数据集中的剩余数据的判断，因而能提高性能。而关联是同等价匹配（=）实现的，不适合空值数据。因此，关联字段可能存在空值时，无法采用ANTI-JOIN。

注意：无论是否存在空值，NOT EXISTS都可以采用ANTI-JOIN，但是它的逻辑结果与NOT IN并不等价。在以下例子中，NOT EXISTS和NOT IN的结果不相同：

统计信息如下

另外一种情况

统计信息如下：

事务回滚

HELLODBA.COM>rollback;  

Rollback complete.  

如何加入"云和恩墨大讲堂"微信群

搜索 盖国强（Eygle）微信号：eyygle，或者扫描下面二维码，备注：云和恩墨大讲堂，即可入群。每周与千人共享免费技术分享，与讲师在线讨论。

近期文章

资源下载

关注本微信（OraNews）回复关键字获取

2016DTCC, 2016数据库大会PPT；

DBALife,"DBA的一天"精品海报大图；

12cArch,“Oracle 12c体系结构”精品海报；

DBA01，《Oracle DBA手记》第一本下载；

YunHe，“云和恩墨大讲堂”案例文档下载；